Integrantes:

• Bruno Pereira Fornaro - B44398
• Lorenzo Aguiar de Alencastro Guimarães - B44407
• Vanessa Berwanger Wille - B43918

Introdução

Este trabalho trata da Avaliação 2 da disciplina de Banco de Dados do curso Ciêncisa de Dados/FGV-EMAp objetivando aperfeiçoar e colocar em prática os aprendizados do semestre. Neste caso, a proposta é capturar dados GeoJson, armanezar no banco MySql e, posteriormente, analisar as informações.

Para tanto, capturamos os dados de Arquivos GeoJSON do Estado de São Paulo e datasets com informações adicionais sobre os municípios, como o Índice de Nível Socioeconômico, o Índice de Desenvolvimento da Educação do Estado de São Paulo, fluxo escolar, a quantidade de população por município e referências sobre a segurança, extraídos do site Base dos Dados, afim de entedermos como essas estão distribuídas no espaço geográfico do Estado, averiguando quais municípios se destacam e como os dados se correlacionam.

Importando as bibliotecas necessárias

# Bibliotecas para ler os dados dos arquivos e para manipulação (transformação) de dados
import os
import sys
import time
import datetime
import numpy as np
import pandas as pd
import pathlib
import geopandas as gpd
from sklearn.preprocessing import MaxAbsScaler
abs_scaler = MaxAbsScaler()

# Bibliotecas para visualização
import geoplot.crs as gcrs
import geoplot as gplt
import matplotlib.pyplot as plt
import matplotlib as ptl
import seaborn as sns
import plotly.express as px
from plotly.subplots import make_subplots

# Para imprimir os gráficos inline
%matplotlib inline 

# Bibliotecas para conectar com o MySQL
import getpass
import pymysql
import sqlalchemy
from sqlalchemy import create_engine
import pandas.io.sql as psql

# Para salvar os gráficos interativos
import plotly.io as pio
pio.renderers.default = "notebook"

Importando e limpando os dados

Para esse trabalho pretendemos utilizar 5 diferentes tabelas, uma para os dados geométricos, do Geodata BR, e outras quatro com dados a respeito da educação, segurança e população do estado de São Paulo, da Base dos dados. Dessa forma, vamos primeiramente importar os dados dos arquivos GeoJSON e CSV para o pandas e o geopandas para limpá-los e posteriormente colocá-los no MySQL utilizando o pymysql e o sqlalchemy.

Geometria

Começaremos pelos dados de geometria:

# Lendo o arquivo GeoJSON e importando os dados para um geodataframe
geometria = gpd.read_file('.//bases_de_dados//geojs-35-mun.json')

# Alterando o indice da tabela (para não ficar duplicado)
geometria.set_index("id", drop=True, inplace=True)

# Imprimindo os tipos de dados (para conferir se foi reconhecida a coluna de geometria)
print(geometria.dtypes)

# Imprimindo os primeiros valores da tabela
geometria.head(3)

name             object
description      object
geometry       geometry
dtype: object

	name	description	geometry
id
3500105	Adamantina	Adamantina	POLYGON ((-51.05787 -21.39888, -51.05365 -21.4...
3500204	Adolfo	Adolfo	POLYGON ((-49.65478 -21.20607, -49.63847 -21.2...
3500303	Aguaí	Aguaí	POLYGON ((-47.20890 -21.97129, -47.20297 -21.9...

A primeira vista, as colunas name e description parecem ser iguais. Caso sejam, desejados armazenar apenas uma delas. Conferindo se as colunas nome e descrição são iguais:

contador = 0

# Verifica se os elementos das colunas são iguais para cada linha
for row in geometria.iterrows():
    if row[1]["name"] != row[1]["description"]:
        print("Existe nome diferente da descrição")
        contador += 1
        
# Se não foi encontrada nenhuma linha
if contador == 0:
    print("As duas colunas são iguais")

As duas colunas são iguais

Como vimos acima, as duas colunas são iguais, então vamos remover a coluna description:

# Removendo a coluna
geometria.drop('description', axis='columns', inplace=True)

#Exibindo o resultado
geometria.head(3)

	name	geometry
id
3500105	Adamantina	POLYGON ((-51.05787 -21.39888, -51.05365 -21.4...
3500204	Adolfo	POLYGON ((-49.65478 -21.20607, -49.63847 -21.2...
3500303	Aguaí	POLYGON ((-47.20890 -21.97129, -47.20297 -21.9...

Vamos verificar o nome de maior tamanho na coluna name para posteriormente criar a tabela no MySQL (esse processo também foi feito para as outras colunas, mas não exibiremos esse processo para todas as colunas para não ser excessivamente repetitivo)

geometria["name"].apply(lambda x: len(x)).max()

26

Com isso, vamos fazer de forma análoga para as próximas tabelas

IDESP das Escolas

Nesse caso, diferentemente de como foi feito anteriormente, também precisamos remover valores faltantes na coluna com o id do município (código do IBGE - o id da tabela anterior). Abaixo vamos ler o arquivo CSV e remover os valores faltantes:

# Lendo o arquivo CSV e importando os dados para um dataframe
idesp = pd.read_csv('.//bases_de_dados//escola_IDESP.csv')

# Removendo os valores faltantes em "id_municipio"
idesp = idesp[~np.isnan(idesp["id_municipio"])]

# Transformando os valores de "id_municipio" em inteiros
idesp["id_municipio"] = idesp["id_municipio"].apply(lambda x: int(x))

# Imprimendo o resultado
idesp.head(3)

	ano	id_municipio	id_escola	id_escola_sp	nota_idesp_ef_iniciais	nota_idesp_ef_finais	nota_idesp_em
0	2007	3550308	35000024	24	2.88	2.52	1.32
1	2007	3550308	35000048	48	NaN	2.26	0.93
2	2007	3550308	35000061	61	3.02	NaN	NaN

Nível Sócio Economico das Escolas

De forma análoga as tabelas anteriores:

# Lendo o arquivo CSV e importando os dados para um dataframe
escolas_nse = pd.read_csv('.//bases_de_dados//escola_nse.csv')

# Exibindo o resultado
escolas_nse.head(3)

	id_municipio	rede	diretoria	id_escola	id_escola_sp	nivel_socio_economico
0	3550308	estadual	NORTE 1	35000012	12	2.71
1	3550308	estadual	NORTE 1	35000024	24	3.93
2	3550308	estadual	NORTE 1	35000036	36	4.02

Fluxo Escolar

Nesse caso, além do que já foi feito anteriormente, também precisamos remover valores inconsistentes pois há campos na tabela com proporções maiores que 100%. Fazemos isso abaixo:

# Lendo o arquivo CSV e importando os dados para um dataframe
fluxo_escolar = pd.read_csv('.//bases_de_dados//fluxo_escolar.csv')

# Removendo os valores faltantes em "id_municipio"
fluxo_escolar = fluxo_escolar[~np.isnan(fluxo_escolar["id_municipio"])]

# Transformando os valores de "id_municipio" em inteiros
fluxo_escolar["id_municipio"] = fluxo_escolar["id_municipio"].apply(lambda x: int(x))

# Removendo os dados inconsistentes (proporção maior que 100%)
fluxo_escolar = fluxo_escolar[~(fluxo_escolar["prop_aprovados_em"] > 100)]
fluxo_escolar = fluxo_escolar[~(fluxo_escolar["prop_reprovados_em"] > 100)]
fluxo_escolar = fluxo_escolar[~(fluxo_escolar["prop_abandono_em"] > 100)]

# Exibindo o resultado
fluxo_escolar.head(3)

	ano	sigla_uf	rede	diretoria	id_municipio	id_escola	id_escola_sp	codigo_tipo_escola	prop_aprovados_anos_inciais_ef	prop_reprovados_anos_iniciais_ef	prop_abandono_anos_iniciais_ef	prop_aprovados_anos_finais_ef	prop_reprovados_anos_finais_ef	prop_abandono_anos_finais_ef	prop_aprovados_em	prop_reprovados_em	prop_abandono_em
0	2011	SP	estadual	NORTE 1	3550308	35910995	910995	NaN	0.000000	0.000000	0.000000	0.0	0.0	0.0	65.032257	32.774193	2.193548
1	2011	SP	estadual	NORTE 1	3550308	35910296	910296	NaN	98.034935	0.655022	1.310044	0.0	0.0	0.0	0.000000	0.000000	0.000000
2	2011	SP	estadual	NORTE 1	3550308	35923618	923618	NaN	97.419357	0.967742	1.612903	0.0	0.0	0.0	0.000000	0.000000	0.000000

Ocorrências Registrasdas em São Paulo

De forma análoga as tabelas anteriores:

# Lendo o arquivo CSV e importando os dados para um dataframe
ocorrencias_sp = pd.read_csv('.//bases_de_dados//ocorrencias_registradas.csv')

# Exibindo o resultado
ocorrencias_sp.head(3)

	ano	mes	id_municipio	regiao_ssp	homicidio_doloso	numero_de_vitimas_em_homicidio_doloso	homicidio_doloso_por_acidente_de_transito	numero_de_vitimas_em_homicidio_doloso_por_acidente_de_transito	homicidio_culposo_por_acidente_de_transito	homicidio_culposo_outros	...	total_de_estupro	estupro	estupro_de_vulneravel	total_de_roubo_outros	roubo_outros	roubo_de_veiculo	roubo_a_banco	roubo_de_carga	furto_outros	furto_de_veiculo
0	2002	1	3500105	Presidente Prudente	0.0	0.0	0.0	0.0	0.0	0.0	...	0.0	NaN	NaN	0.0	NaN	0.0	0.0	0.0	21.0	0.0
1	2002	2	3500105	Presidente Prudente	0.0	0.0	0.0	0.0	0.0	0.0	...	0.0	NaN	NaN	0.0	NaN	0.0	0.0	0.0	32.0	0.0
2	2002	3	3500105	Presidente Prudente	0.0	0.0	0.0	0.0	0.0	0.0	...	0.0	NaN	NaN	0.0	NaN	0.0	0.0	0.0	36.0	0.0

3 rows × 27 columns

População de São Paulo

Além do que já foi feito anteriormente, nesse caso desejamos manter apenas os dados referentes ao estado de São Paulo, já que nas tabelas anteriores todos os dados já eram de São Paulo, porém nessa os dados são do Brasil inteiro. Fazemos isso abaixo:

# Lendo o arquivo CSV e importando os dados
populacao_brasil = pd.read_csv('.//bases_de_dados//municipio.csv')

# Mantendo somente os dados de cidades do estado de São Paulo
populacao_sp = populacao_brasil[populacao_brasil["id_municipio"].isin(geometria.index.astype(int))].copy()

# Exibindo o resultado
populacao_sp.head(3)

	ano	sigla_uf	id_municipio	populacao
98994	1991	SP	3500105	32092.0
98995	1992	SP	3500105	32085.0
98996	1993	SP	3500105	32099.0

Criando o banco de dados e as tabelas no MySQL

Criando o modelo físico

Agora que já temos os dados limpos adequadamente, desejamos criar um banco de dados no MySQL para armazenar todos os dados que temos. Para isso, primeiramente criamos um modelo físico no Vertabelo para nos auxiliar:

Vale lembar que os tipos de dados foram definidos de acordo como já vimos anteriormente quando limpamos eles, assim como verificamos também os casos que temos valores faltantes (sendo que muitos deles desejamos manter assim, sem remover essas observações)

Criando a conexão com o MySQL

Devemos criar uma conexão com o MySQL para conseguir criar o banco de dados e as tabelas utilizando python. Para isso, nesta etapa, utilizaremos o pymysql:

# Recebendo a senha do usuário (foi utilizado o getpass para cada usuário inserir sua senha e ela não ser exibida)
p = getpass.getpass()

# Criando a conexão com o MySQL
connection = pymysql.connect(host='localhost', port=3306, user='root', passwd=p) #, db='mysql')

# Criando um cursor
cursor = connection.cursor()

# Ativando o autocommit (para não ser necessário realizar um commit a cada alteração)
connection.autocommit(True)

········

Criando o banco de dados

Como estamos trabalhando coletivamente e esse notebook deve funcionar para rodar as "células" em sequência e funcionar todas as etapas, devemos remover (apagar, deletar) a base de dados caso exista no computador do respectivo usuário e criar ela novamente:

# Apagando a base de dados caso exista
cursor.execute("DROP DATABASE IF EXISTS educacao_sao_paulo;")

# Criando a base de dados
cursor.execute("create database educacao_sao_paulo;")

1

Usando a base de dados criada

Agora que criamos a base de dados, devemos usá-la para poder prosseguir com a etapa de criar tabelas. Utilizamos o nosso cursor novamente e passamos a utilizar a base de dados criada com o comando do MySQL:

# Definindo o uso da nossa base de dados criada anteriormente
cursor.execute("use educacao_sao_paulo;")

0

Criando as tabelas

Utilizando o modelo que foi criado no Vertabelo, agora vamos criar nossas bases de dados no MySQL. Abaixo, criaremos as respectivas tabelas para cada subtópico.

Geometria

cursor.execute("""
    CREATE TABLE geometria (
    id int NOT NULL,
    name varchar(40) NOT NULL,
    geometry polygon NOT NULL,
    CONSTRAINT geometria_pk PRIMARY KEY (id)
);""")

0

Nível Sócio Econômico das Escolas

cursor.execute("""
    CREATE TABLE escola_nse (
    id int NOT NULL AUTO_INCREMENT,
    id_municipio int NOT NULL,
    rede varchar(40) NOT NULL,
    diretoria varchar(40) NOT NULL,
    id_escola_sp int NOT NULL,
    id_escola int NOT NULL,
    nivel_socio_economico float(6,3) NULL,
    CONSTRAINT escola_nse_pk PRIMARY KEY (id)
);
                """)

0

Fluxo Escolar

cursor.execute("""
    CREATE TABLE fluxo_escolar (
    id int NOT NULL AUTO_INCREMENT,
    id_municipio int NOT NULL,
    ano int NOT NULL,
    sigla_uf varchar(2) NOT NULL,
    rede varchar(40) NULL,
    diretoria varchar(40) NOT NULL,
    id_escola int NOT NULL,
    id_escola_sp int NOT NULL,
    codigo_tipo_escola int NULL,
    prop_aprovados_anos_inciais_ef float(11,8) NULL,
    prop_reprovados_anos_iniciais_ef float(11,8) NULL,
    prop_abandono_anos_iniciais_ef float(11,8) NULL,
    prop_aprovados_anos_finais_ef float(11,8) NULL,
    prop_reprovados_anos_finais_ef float(11,8) NULL,
    prop_abandono_anos_finais_ef float(11,8) NULL,
    prop_aprovados_em float(11,8) NULL,
    prop_reprovados_em float(11,8) NULL,
    prop_abandono_em float(11,8) NULL,
    CONSTRAINT fluxo_escolar_pk PRIMARY KEY (id)
);
                """)

0

IDESP

cursor.execute("""
    CREATE TABLE idesp (
    id int NOT NULL AUTO_INCREMENT,
    id_municipio int NOT NULL,
    ano int NOT NULL,
    id_escola int NOT NULL,
    id_escola_sp int NOT NULL,
    nota_idesp_ef_iniciais float(6,3) NULL,
    nota_idesp_ef_finais float(6,3) NULL,
    nota_idesp_em float(6,3) NULL,
    CONSTRAINT idesp_pk PRIMARY KEY (id)
);
                """)

0

Ocorrências Registradas em São Paulo

cursor.execute("""
    CREATE TABLE ocorrencias_sp (
    id int NOT NULL AUTO_INCREMENT,
    id_municipio int NOT NULL,
    ano int NOT NULL,
    mes int NOT NULL,
    regiao_ssp varchar(40) NOT NULL,
    homicidio_doloso int NULL,
    numero_de_vitimas_em_homicidio_doloso int NULL,
    homicidio_doloso_por_acidente_de_transito int NULL,
    numero_de_vitimas_em_homicidio_doloso_por_acidente_de_transito int NULL,
    homicidio_culposo_por_acidente_de_transito int NULL,
    homicidio_culposo_outros int NULL,
    tentativa_de_homicidio int NULL,
    lesao_corporal_seguida_de_morte int NULL,
    lesao_corporal_dolosa int NULL,
    lesao_corporal_culposa_por_acidente_de_transito int NULL,
    lesao_corporal_culposa_outras int NULL,
    latrocinio int NULL,
    numero_de_vitimas_em_latrocinio int NULL,
    total_de_estupro int NULL,
    estupro int NULL,
    estupro_de_vulneravel int NULL,
    total_de_roubo_outros int NULL,
    roubo_outros int NULL,
    roubo_de_veiculo int NULL,
    roubo_a_banco int NULL,
    roubo_de_carga int NULL,
    furto_outros int NULL,
    furto_de_veiculo int NULL,
    CONSTRAINT ocorrencias_sp_pk PRIMARY KEY (id)
);
                """)

0

População de São Paulo

cursor.execute("""
    CREATE TABLE populacao_sp (
    id int NOT NULL AUTO_INCREMENT,
    ano int NOT NULL,
    sigla_uf varchar(2) NOT NULL,
    id_municipio int NOT NULL,
    populacao int NULL,
    CONSTRAINT populacao_sp_pk PRIMARY KEY (id)
);
                """)

0

Populando as tabelas

Agora que já criamos as tabelas corretamente no MySQL, desejamos popular nossa base de dados. Para isso, utilizaremos o pymysql e o sqlalchemy, já que temos uma tabela com dados de geometria e as demais não possuem.

Geometria

Populamos a tabela geometria utilizando o pymysql (pois o geopandas oferece métodos para popular tabelas no ProgreSQL, mas não no MySQL):

# Iteremos em cada linha da tabela de geometria para inserí-las uma a uma no MySQL
for row in geometria.iterrows():
    # Criamos uma tupla com os dados necessários
    data = (row[0], row[1]["name"], row[1]["geometry"].wkt)
    
    # Criamos uma string com o comando para popular a tabela no MySQL (com valores a serem preenchidos pelos dados da respectiva linha)
    sql = """INSERT INTO geometria (id, name, geometry) VALUES (%s, %s, ST_GeomFromText(%s, 0))"""
    
    # Inserimos os dados linha a linha
    cursor.execute(sql,data)

Vale lembrar que acima convertemos os dados de geometria ao inserir no MySQL para que o tipo de dado seja aceito como polygon no MySQL.

IDESP

Para as próximas tabelas utilizaremos o sqlalchemy, então devemos criar uma conexão por ele:

# Criando a conexão (utilizando a senha já inserida anteriormente)
conectar = f'mysql+pymysql://root:{p}@localhost/educacao_sao_paulo'
engine = create_engine(conectar)

Agora, podemos popular a tabela dos dados do IDESP, utilizando o método to_sql() que o pandas oferece:

idesp.to_sql('idesp', con=engine, index=False, if_exists="append")

Faremos de forma análoga para as próximas tabelas, nos próximos subtópicos abaixo

Nível Sócio Economico das Escolas

escolas_nse.to_sql('escola_nse', con=engine, index=False, if_exists="append")

Fluxo Escolar

fluxo_escolar.to_sql('fluxo_escolar', con=engine, index=False, if_exists="append")

Ocorrências Registrasdas em São Paulo

ocorrencias_sp.to_sql('ocorrencias_sp', con=engine, index=False, if_exists="append")

População de São Paulo

populacao_sp.to_sql('populacao_sp', con=engine, index=False, if_exists="append")

Adicionando as Foreign Keys

Agora que já populamos as tabelas, podemos adicionar as foreign keys, conforme nosso modelo físico criado no Vertabelo. Isso não foi feito anteriormente para não haver problemas ao popular a base de dados, pois a ordem para inserior os dados seria mais importante, sendo assim adicionar as foreign keys após popular as tabelas é mais conveniente. Adicionando as foreign keys:

# Adicionando a foreign key a tabela "escola_nse"
cursor.execute("""
ALTER TABLE escola_nse ADD CONSTRAINT escola_nse_geometria FOREIGN KEY escola_nse_geometria (id_municipio)
    REFERENCES geometria (id);
""")

# Adicionando a foreign key a tabela "fluxo_escolar"
cursor.execute("""
ALTER TABLE fluxo_escolar ADD CONSTRAINT fluxo_escolar_geometria FOREIGN KEY fluxo_escolar_geometria (id_municipio)
    REFERENCES geometria (id);
""")

# Adicionando a foreign key a tabela "idesp"
cursor.execute("""
ALTER TABLE idesp ADD CONSTRAINT idesp_geometria FOREIGN KEY idesp_geometria (id_municipio)
    REFERENCES geometria (id);
""")

# Adicionando a foreign key a tabela "ocorrencias_sp"
cursor.execute("""
ALTER TABLE ocorrencias_sp ADD CONSTRAINT ocorrencias_sp_geometria FOREIGN KEY ocorrencias_sp_geometria (id_municipio)
    REFERENCES geometria (id);
""")

# Adicionando a foreign key a tabela "populacao_sp"
cursor.execute("""
ALTER TABLE populacao_sp ADD CONSTRAINT populacao_sp_geometria FOREIGN KEY populacao_sp_geometria (id_municipio)
    REFERENCES geometria (id);
""")

19842

Importando os dados para o pandas e o geopandas

Agora que conseguimos popular o banco de dados no MySQL corretamente, desejamos importá-los novamente para o pandas e o geopandas para que possamos fazer as análises com os dados. Utilizaremos nomes de variáveis diferentes para reforçar que estamos utilizando os dados que foram populados no MySQL.

Abaixo importaremos os dados dos repectivos subtópicos.

Geometria

# Importando os dados desejados para o pandas e convertendo a geometria do MySQL novamente para ser possível ler no geopandas
mapassp = psql.read_sql('SELECT id, name, ST_AsWKT(geometry) as geometry FROM geometria;', con=connection)

# Convertendo o tipo de dado da geometria com o geopandas para ser reconhecido como geometria 
mapassp['geometry'] = gpd.GeoSeries.from_wkt(mapassp['geometry'])

# Convertendo o DataFrame para um GeoDataFrame
mapassp = gpd.GeoDataFrame(mapassp, geometry="geometry")

# Imprimindo os tipos de dados
print(mapassp.dtypes)

# Imprimendo o resultado da importação
mapassp.head(3)

id             int64
name          object
geometry    geometry
dtype: object

	id	name	geometry
0	3500105	Adamantina	POLYGON ((-51.05787 -21.39888, -51.05365 -21.4...
1	3500204	Adolfo	POLYGON ((-49.65478 -21.20607, -49.63847 -21.2...
2	3500303	Aguaí	POLYGON ((-47.20890 -21.97129, -47.20297 -21.9...

Como podemos ver acima, conseguimos importar nossos dados novamente e fazê-los serem reconhecidos como geometria pelo geopandas.

Nível Sócio Econômico

# Importandos os dados para um DataFrame do pandas
sp_nse = psql.read_sql('SELECT * FROM escola_nse;', con=connection)

# Exibindo o resultado da importação
sp_nse.head(3)

	id	id_municipio	rede	diretoria	id_escola_sp	id_escola	nivel_socio_economico
0	1	3550308	estadual	NORTE 1	12	35000012	2.71
1	2	3550308	estadual	NORTE 1	24	35000024	3.93
2	3	3550308	estadual	NORTE 1	36	35000036	4.02

IDESP das Escolas

# Importandos os dados para um DataFrame do pandas
sp_idesp = psql.read_sql('SELECT * FROM idesp;', con=connection)

# Exibindo o resultado da importação
sp_idesp.head(3)

	id	id_municipio	ano	id_escola	id_escola_sp	nota_idesp_ef_iniciais	nota_idesp_ef_finais	nota_idesp_em
0	1	3550308	2007	35000024	24	2.88	2.52	1.32
1	2	3550308	2007	35000048	48	NaN	2.26	0.93
2	3	3550308	2007	35000061	61	3.02	NaN	NaN

Fluxo Escolar

# Importandos os dados para um DataFrame do pandas
sp_fluxo_escolar = psql.read_sql('SELECT * FROM fluxo_escolar;', con=connection)

# Exibindo o resultado da importação
sp_fluxo_escolar.head(3)

	id	id_municipio	ano	sigla_uf	rede	diretoria	id_escola	id_escola_sp	codigo_tipo_escola	prop_aprovados_anos_inciais_ef	prop_reprovados_anos_iniciais_ef	prop_abandono_anos_iniciais_ef	prop_aprovados_anos_finais_ef	prop_reprovados_anos_finais_ef	prop_abandono_anos_finais_ef	prop_aprovados_em	prop_reprovados_em	prop_abandono_em
0	1	3550308	2011	SP	estadual	NORTE 1	35910995	910995	NaN	0.000000	0.000000	0.000000	0.0	0.0	0.0	65.032257	32.774193	2.193548
1	2	3550308	2011	SP	estadual	NORTE 1	35910296	910296	NaN	98.034935	0.655022	1.310044	0.0	0.0	0.0	0.000000	0.000000	0.000000
2	3	3550308	2011	SP	estadual	NORTE 1	35923618	923618	NaN	97.419357	0.967742	1.612903	0.0	0.0	0.0	0.000000	0.000000	0.000000

Ocorrências Registrasdas em São Paulo

# Importandos os dados para um DataFrame do pandas
df_seguranca = psql.read_sql('SELECT * FROM ocorrencias_sp;', con=connection)

# Exibindo o resultado da importação
df_seguranca.head(3)

	id	id_municipio	ano	mes	regiao_ssp	homicidio_doloso	numero_de_vitimas_em_homicidio_doloso	homicidio_doloso_por_acidente_de_transito	numero_de_vitimas_em_homicidio_doloso_por_acidente_de_transito	homicidio_culposo_por_acidente_de_transito	...	total_de_estupro	estupro	estupro_de_vulneravel	total_de_roubo_outros	roubo_outros	roubo_de_veiculo	roubo_a_banco	roubo_de_carga	furto_outros	furto_de_veiculo
0	1	3500105	2002	1	Presidente Prudente	0.0	0.0	0.0	0.0	0.0	...	0.0	NaN	NaN	0.0	NaN	0.0	0.0	0.0	21.0	0.0
1	2	3500105	2002	2	Presidente Prudente	0.0	0.0	0.0	0.0	0.0	...	0.0	NaN	NaN	0.0	NaN	0.0	0.0	0.0	32.0	0.0
2	3	3500105	2002	3	Presidente Prudente	0.0	0.0	0.0	0.0	0.0	...	0.0	NaN	NaN	0.0	NaN	0.0	0.0	0.0	36.0	0.0

3 rows × 28 columns

População de São Paulo

# Importandos os dados para um DataFrame do pandas
sp_populacao = psql.read_sql('SELECT * FROM populacao_sp;', con=connection)

# Exibindo o resultado da importação
sp_populacao.head(3)

	id	ano	sigla_uf	id_municipio	populacao
0	1	1991	SP	3500105	32092.0
1	2	1992	SP	3500105	32085.0
2	3	1993	SP	3500105	32099.0

Realizando as Análises e Visualizações

Como já importamos os dados devidamente, agora podemos fazer as análises e visualizações que desejamos com os dados que temos. Pretendemos fazer gráficos interativos, mas precisamos salva-los, então vamos configurar o chart studio para salvar esses gráficos:

# usuario = getpass.getpass()
# senha = getpass.getpass()
# chart_studio.tools.set_credentials_file(username=usuario, api_key=senha)
# chart_studio.tools.set_config_file(world_readable=True, sharing='public')

Índice de Nível Socieconômico

Para esta análise, usou-se uma tabela que contém dados que caracterizam brevemente a escola e, principalmente, dados do indicador socioeconômico da escola, o INSE. O indicador varia de 0 a 10, sendo 10 a escola com o nível socioeconômico mais baixo e 0 a escola com nível mais alto. O INSE é calculado a partir dos questionários do SARESP. Ele considera as seguintes variáveis: grau de escolaridade dos pais, posse de bens de consumo duráveis na residência e renda familiar.

Inicialmente, realizou-se a leitura da base com Geopandas e uma filtragem nos dados, para trabalhar somente com o necessário. Posteriormente, ainda, foram unidas as bases de dados com informações espaciais e do INSE do munícipio.

#selecionando as colunas necessárias
sp_nse = sp_nse[['id_municipio','nivel_socio_economico']].copy()
#tranformando em numérico
sp_nse["nivel_socio_economico"] = pd.to_numeric(sp_nse["nivel_socio_economico"]).copy()
#transformando NaN em 0
sp_nse.fillna(0, inplace = True)
#Renomeando coluna id_municipio para posteriormente juntar as tabelas
sp_nse.rename(columns={"id_municipio": "id"}, inplace = True)
#agrupando por id e tirando a média
sp_nse=sp_nse.groupby("id").mean()
#juntando as tabelas com a geometria e os dados por id
sp_nse_f = mapassp.join(sp_nse, on="id", how="left")
sp_nse_f

	id	name	geometry	nivel_socio_economico
0	3500105	Adamantina	POLYGON ((-51.05787 -21.39888, -51.05365 -21.4...	4.000000
1	3500204	Adolfo	POLYGON ((-49.65478 -21.20607, -49.63847 -21.2...	4.190000
2	3500303	Aguaí	POLYGON ((-47.20890 -21.97129, -47.20297 -21.9...	5.119091
3	3500402	Águas da Prata	POLYGON ((-46.70755 -21.82895, -46.70380 -21.8...	5.600000
4	3500501	Águas de Lindóia	POLYGON ((-46.61147 -22.43496, -46.60302 -22.4...	3.940000
...	...	...	...	...
640	3557006	Votorantim	POLYGON ((-47.37268 -23.51783, -47.37047 -23.5...	4.268462
641	3557105	Votuporanga	POLYGON ((-50.02073 -20.33347, -50.01275 -20.3...	4.268889
642	3557154	Zacarias	POLYGON ((-49.99521 -21.03253, -49.98434 -21.0...	4.450000
643	3557204	Chavantes	POLYGON ((-49.68539 -22.94866, -49.68396 -22.9...	5.020000
644	3557303	Estiva Gerbi	POLYGON ((-46.91399 -22.19933, -46.91221 -22.2...	3.860000

645 rows × 4 columns

Na primeira plotagem, abaixo, é possível visualizar os nível socioeconômico (média das escolas) por município.

#plotagem do mapa do INSE por munícipio
fig, ax = plt.subplots(figsize=(10, 10))
sp_nse_f.plot(ax=ax, column='nivel_socio_economico', cmap='coolwarm', legend=True,
                legend_kwds={'label': "Nível socioeconômico", 'shrink': 0.3} )
plt.title('Nível socioeconômico (média das escolas) por município') 
ax.axis('off');

Pelo mapa, é vísivel que alguns munícipios possuem níveis socioeconômicos (média das escolas) mais elevados que outros. O município com melhor índice de nível socioecônomico é águas de São Pedro (1.66), já com o pior INSE, é Itaóca (8.56), como verificado pelos gráficos de barras a seguir, que indicam os 10 melhores e 10 piores INSE.

df = sp_nse_f[['name','nivel_socio_economico']] #tabela com os dados necessários para fazer os gráficos de barra
df1=df.sort_values('nivel_socio_economico', ascending=False).reset_index().head(10) #ordenação decrescente dos dados 
df2=df.sort_values('nivel_socio_economico').reset_index().head(10)#ordenação crsescente dos dados

#plotagem do gráfico de barra dos 10 piores INSE com Plotly
fig = px.bar(df1,
             x='name', y="nivel_socio_economico", height=500, width=700,
             barmode='stack', labels={"name": "Nome", 'nivel_socio_economico':"Nível socioeconômico "})
fig.update_layout(title = '10 municípios com piores níveis socioeconômicos (média das escolas)')
fig.update_xaxes(title = 'Município')
fig.update_yaxes(title = 'Nível socioeconômico médio das escolas')
fig.show()

#plotagem do gráfico de barra dos 10 melhores INSE com Plotly
fig = px.bar(df2,
             x='name', y="nivel_socio_economico", height=500, width=700,
             barmode='stack', labels={"name": "Nome", 'nivel_socio_economico':"Nível socioeconômico "})
fig.update_layout(title = '10 municípios com melhores nível socioeconômico (média das escolas)')
fig.update_xaxes(title = 'Município')
fig.update_yaxes(title = 'Nível socioeconômico médio das escolas')
fig.show()

Índice de Desenvolvimento da Educação do Estado de São Paulo

A tabela usada, nesse caso, contém dados da nota do Índice de Desenvolvimento da Educação do Estado de São Paulo (IDESP) para cada escola da rede estadual do estado de São Paulo, estabelecendo metas que as escolas devem alcançar ano a ano e seus resultados. Disponibilizada pela Secretaria de Educação do Estado de São Paulo, a tabela identifica cada escola através de códigos nacionais e estaduais e disponibiliza variáveis que demonstram para aquela dada escola, seu aproveitamento no IDESP. O IDESP é um indicador que avalia a qualidade da escola, sendo composto por dois critérios: o desempenho dos alunos nos exames de proficiência do SARESP (o quanto aprenderam) e o fluxo escolar (em quanto tempo aprenderam).Assim, nesta avaliação, considera–se que uma boa escola é aquela em que a maior parte dos alunos aprende as competências e habilidades requeridas para a sua série/ano, num período de tempo ideal – o ano letivo.

Para a realização das análises, novamente realizou-se uma limpeza nos dados e selecionou-se o ano de 2018.

#selecionando as colunas necessárias
sp_idesp = sp_idesp[["ano",'id_municipio','nota_idesp_ef_iniciais', "nota_idesp_ef_finais", "nota_idesp_em"]]
#tranformando em numéricos
sp_idesp['nota_idesp_ef_iniciais'] = pd.to_numeric(sp_idesp["nota_idesp_ef_iniciais"])
sp_idesp['nota_idesp_ef_finais'] = pd.to_numeric(sp_idesp["nota_idesp_ef_finais"])
sp_idesp['nota_idesp_em'] = pd.to_numeric(sp_idesp["nota_idesp_em"])
#mudando nome da coluna
sp_idesp.rename(columns={"id_municipio": "id"}, inplace = 1)
#selecionando os dados do ano de 2018
sp_idesp_2018_na = sp_idesp[sp_idesp["ano"] == 2018]
#agrupando por id e tirando a média
sp_idesp_2018_na=sp_idesp_2018_na.groupby("id").mean()
#juntando as tabelas dos dados e da geometria
sp_idesp_2018 = mapassp.join(sp_idesp_2018_na, on="id", how="left")
#tranformando NaN em 0
sp_idesp_2018.fillna(0, inplace = True)

sp_idesp_2018

	id	name	geometry	ano	nota_idesp_ef_iniciais	nota_idesp_ef_finais	nota_idesp_em
0	3500105	Adamantina	POLYGON ((-51.05787 -21.39888, -51.05365 -21.4...	2018.0	0.00	3.423333	2.250000
1	3500204	Adolfo	POLYGON ((-49.65478 -21.20607, -49.63847 -21.2...	2018.0	0.00	0.000000	3.170000
2	3500303	Aguaí	POLYGON ((-47.20890 -21.97129, -47.20297 -21.9...	2018.0	6.70	2.943333	2.436667
3	3500402	Águas da Prata	POLYGON ((-46.70755 -21.82895, -46.70380 -21.8...	2018.0	0.00	4.250000	3.110000
4	3500501	Águas de Lindóia	POLYGON ((-46.61147 -22.43496, -46.60302 -22.4...	2018.0	6.97	3.650000	3.630000
...	...	...	...	...	...	...	...
640	3557006	Votorantim	POLYGON ((-47.37268 -23.51783, -47.37047 -23.5...	2018.0	0.00	3.728462	2.754615
641	3557105	Votuporanga	POLYGON ((-50.02073 -20.33347, -50.01275 -20.3...	2018.0	0.00	3.665556	2.413750
642	3557154	Zacarias	POLYGON ((-49.99521 -21.03253, -49.98434 -21.0...	2018.0	0.00	2.800000	2.650000
643	3557204	Chavantes	POLYGON ((-49.68539 -22.94866, -49.68396 -22.9...	2018.0	0.00	2.620000	2.560000
644	3557303	Estiva Gerbi	POLYGON ((-46.91399 -22.19933, -46.91221 -22.2...	2018.0	0.00	0.000000	3.560000

645 rows × 7 columns

No mapas a seguir, pode-se visualizar, inicialmente, que as escolas estaduais no estado de São Paulo, em geral, abordam o Ensino Médio, já as séries iniciais possuem muitos valores vazios por município, ou seja, não possuem avaliação do IDESP pois, provavelmente, são escolas municipais que fornecem esse nível de educação.

Ainda, até 2030, a meta é que as escolas alcancem índice 7 para o ciclo que vai do 1° ao 5° ano, índice 6 para o ciclo que vai do 6° ao 9° ano e índice 5 para o Ensino Médio. Pelas plotagens, a séries iniciais parecem estar mais próximas do objetivo, mesmo que tenham que alcançar notas mais altas.

#plotagem de gráficos do IDESP por nível de ensino
fig, axs = plt.subplots(3, figsize=(10, 12))

sp_idesp_2018.plot(ax=axs[0], column='nota_idesp_em', cmap='Oranges', legend=True, legend_kwds={'label': "Nota média IDESP", 'shrink': 0.5})
axs[0].set_title('Notas do IDESP no Ensino Médio (média das escolas) por município')

sp_idesp_2018.plot(ax=axs[1], column='nota_idesp_ef_finais', cmap='Oranges', legend=True, legend_kwds={'label': "Nota média IDESP", 'shrink': 0.5})
axs[1].set_title('Notas do IDESP no Ensino Fundamental - série finais - (média das escolas) por município')

sp_idesp_2018.plot(ax=axs[2], column='nota_idesp_ef_iniciais', cmap='Oranges', legend=True, legend_kwds={'label': "Nota média IDESP", 'shrink': 0.5})
axs[2].set_title('Notas do IDESP no Ensino Fundamental - série iniciais - (média das escolas) por município')

plt.tight_layout();

#análises dos IDESP médios do estado por nível
media_efi=sp_idesp_2018_na['nota_idesp_ef_iniciais'].dropna().mean()
media_eff=sp_idesp_2018_na['nota_idesp_ef_finais'].dropna().mean()
media_em=sp_idesp_2018_na['nota_idesp_em'].dropna().mean()

print ("Média IDESP para as séries iniciais:",media_efi)
print ("Média IDESP para as séries finais:",media_eff)
print ("Média IDESP para o Ensino Médio:",media_em)

Média IDESP para as séries iniciais: 5.816202782521359
Média IDESP para as séries finais: 3.5060397587489422
Média IDESP para o Ensino Médio: 2.7797229578798786

Com as médias calculadas, observa-se que, realmente, as séries iniciais, pela média, estão mais próximas de alcançar o objetivo. Contudo, existem munícipios que já alcançaram-no, como verificado pelas tabelas abaixo.

#analisando municípios que já atingiram a meta do IDESP para 2030
obj_efi=sp_idesp_2018[sp_idesp_2018["nota_idesp_ef_iniciais"] >= 7]
print("Munícipio que já atingiram a meta para séries iniciais (analisando a média):")
obj_efi[['name', "nota_idesp_ef_iniciais"]].sort_values('nota_idesp_ef_iniciais', ascending=False)

Munícipio que já atingiram a meta para séries iniciais (analisando a média):

	name	nota_idesp_ef_iniciais
496	Rosana	8.035
191	Gastão Vidigal	7.800
420	Penápolis	7.640
527	Santana da Ponte Pensa	7.560
260	Itápolis	7.430
339	Mirante do Paranapanema	7.170
177	Fernandópolis	7.090
219	Iacri	7.030
253	Itaóca	7.010

obj_efi=sp_idesp_2018[sp_idesp_2018["nota_idesp_ef_finais"] >= 6]
print("Munícipio que já atingiram a meta para séries finais (analisando a média):")
obj_efi[['name', "nota_idesp_ef_finais"]].sort_values('nota_idesp_ef_finais', ascending=False)

Munícipio que já atingiram a meta para séries finais (analisando a média):

	name	nota_idesp_ef_finais
511	Santa Adélia	6.72
316	Macaubal	6.59
527	Santana da Ponte Pensa	6.53
638	Vista Alegre do Alto	6.51
435	Pirajuí	6.00

obj_efi=sp_idesp_2018[sp_idesp_2018["nota_idesp_em"] >= 5]
print("Munícipio que já atingiram a meta para o Ensino Médio (analisando a média):")
obj_efi[['name', "nota_idesp_em"]].sort_values('nota_idesp_em', ascending=False)

Munícipio que já atingiram a meta para o Ensino Médio (analisando a média):

	name	nota_idesp_em
511	Santa Adélia	6.00
316	Macaubal	5.46
572	Sebastianópolis do Sul	5.42
188	Gabriel Monteiro	5.00

Segurança no Estado de São Paulo

Nesse caso, usou-se uma base de dados com informações mensais das ocorrências registradas pela Secretaria de Segurança Publica de São Paulo (SSP). Fez-se a limpeza de dados e acrescentamos uma coluna de ocorrências totais com os dados que supomos relevantes.

#removendo coluna
df_seguranca.drop('id', axis='columns', inplace=True)
#renomeando coluna
df_seguranca.rename(columns={"id_municipio": "id"}, inplace = 1)
#selecionando dados do ano de 2018
df_seguranca_2018 = df_seguranca[df_seguranca["ano"] == 2018]
#agrupando por id e somando os valores
df_seguranca_2018 = df_seguranca_2018.groupby("id").sum()
#juntando as tabelas
df_seguranca_2018= mapassp.join(df_seguranca_2018,on="id", how="left")
#somando colunas com dados relevantes sobre ocorrências de criminalidade
df_seguranca_2018["total"]=df_seguranca_2018["homicidio_doloso"]+df_seguranca_2018["homicidio_culposo_por_acidente_de_transito"]+df_seguranca_2018["homicidio_culposo_outros"]+df_seguranca_2018["tentativa_de_homicidio"]+df_seguranca_2018["lesao_corporal_seguida_de_morte"]+df_seguranca_2018["lesao_corporal_dolosa"]+df_seguranca_2018["lesao_corporal_culposa_outras"]+df_seguranca_2018["homicidio_doloso"]+df_seguranca_2018["latrocinio"]+df_seguranca_2018["total_de_estupro"]+df_seguranca_2018["roubo_outros"]+df_seguranca_2018["roubo_de_veiculo"]+df_seguranca_2018["roubo_a_banco"]+df_seguranca_2018["roubo_de_carga"]+df_seguranca_2018["roubo_de_carga"]+df_seguranca_2018["furto_outros"]+df_seguranca_2018["furto_de_veiculo"]

#plotagem do mapa sobre o total de ocorrências
fig, ax = plt.subplots(figsize=(10, 10))
df_seguranca_2018.plot(ax=ax, column='total', cmap='coolwarm', legend=True, legend_kwds={'shrink': 0.3})
plt.title('Ocorrências de crimes no ano de 2018') 
ax.axis('off');

Na plotagem do mapa do estado com as ocorrências totais, é evidente o quanto São Paulo se destaca com um número de casos bastante elevado em relação aos outros municípios. Já Torre de Pedra é o município com o menor número de ocorrência, apenas 6.

#munícipio com menores números de ocorrências
menores_ocor=df_seguranca_2018.sort_values('total').reset_index().head(10)
menores_ocor[["name", "total"]]

	name	total
0	Torre de Pedra	6.0
1	Dirce Reis	7.0
2	Aspásia	9.0
3	Santa Salete	10.0
4	Arco-Íris	11.0
5	Marinópolis	12.0
6	Vitória Brasil	13.0
7	Trabiju	14.0
8	Paulistânia	14.0
9	Borá	15.0

Contudo, essa soma total de ocorrências pode ser muito influenciada pela quantidade de população do local. Portanto, vale a pena analisar os dados com as proporções. Para isso, fez-se uso de uma base de dados com a quantidade de população por município.

#selecionando os dados sobre a população no ano de 2018
sp_populacao_2018 =sp_populacao[sp_populacao["ano"]==2018]
sp_populacao_2018=sp_populacao_2018[["id_municipio", "populacao"]]
#renomeando coluna
sp_populacao_2018.rename(columns={"id_municipio": "id"}, inplace = 1)
#unindo bases de dados 
df_seg_pop=pd.merge(sp_populacao_2018, df_seguranca_2018, how = 'inner', on = 'id')
#criando coluna com as proporções
df_seg_pop["proporcao"]=df_seg_pop["total"]/df_seg_pop["populacao"]

from geopandas import GeoDataFrame
#plotagem do mapa sobre o total de ocorrências
df_seg_pop = GeoDataFrame(df_seg_pop)
fig, ax = plt.subplots(figsize=(10, 10))
df_seg_pop.plot(ax=ax, column='proporcao', cmap='coolwarm', legend=True, legend_kwds={'shrink': 0.3})
plt.title('Proporção entre ocorrências e população no ano de 2018') 
ax.axis('off');

#munícipios com maiores proporções números de ocorrências por população
menores_ocor=df_seg_pop.sort_values('proporcao', ascending=False).reset_index().head(10)
menores_ocor[["name", "proporcao"]]

	name	proporcao
0	Ilha Comprida	0.062927
1	Mongaguá	0.054099
2	Jeriquara	0.043876
3	Itanhaém	0.041962
4	São Paulo	0.036517
5	Peruíbe	0.036300
6	Diadema	0.033426
7	Praia Grande	0.032982
8	Santo André	0.032045
9	Miracatu	0.030574

Com as novas visualizações, fica notável o quanto ocorreram mudanças trabalhando com proporções. Neste caso, Torre de Pedra continua sendo o munícipio com menor registro de ocorrências. Contudo, Ilha Comprida, Mongaguá, Jeriquara e Itanhaém possuem proporções mais elevadas do que São Paulo, que se destacava no mapa anterior por disparar com o número de ocorrências em relação aos outros munícipios.

Analíse do fluxo escolar do estado de SP

Essa analíse visa a explorar as diferenças no nível de formação escolar dos municipios de São Paulo. As tabelas contém dados do fluxo escolar agregado a nível de ensino de cada escola. Para quantificar o fluxo, utiliza-se 3 variáveis para cada nível de ensino: a proporção de alunos aprovados, a proporção de alunos reprovados e a proporção de alunos que abandonaram o ano escolar.

fluxo_escolar = fluxo_escolar[['id_municipio','prop_aprovados_em', 'prop_reprovados_em', 'prop_abandono_em']]
fluxo_escolar["prop_aprovados_em"] = pd.to_numeric(fluxo_escolar["prop_aprovados_em"])
fluxo_escolar["prop_reprovados_em"] = pd.to_numeric(fluxo_escolar["prop_reprovados_em"])
fluxo_escolar["prop_abandono_em"] = pd.to_numeric(fluxo_escolar["prop_abandono_em"])


fluxo_escolar.fillna(0, inplace = True)
fluxo_escolar.rename(columns={"id_municipio": "id"}, inplace = 1)

fluxo_escolar=fluxo_escolar.groupby("id").mean()

fluxo_escolar = mapassp.join(fluxo_escolar, on="id", how="left")
fluxo_escolar.head(15)

	id	name	geometry	prop_aprovados_em	prop_reprovados_em	prop_abandono_em
0	3500105	Adamantina	POLYGON ((-51.05787 -21.39888, -51.05365 -21.4...	77.656136	9.828708	1.404415
1	3500204	Adolfo	POLYGON ((-49.65478 -21.20607, -49.63847 -21.2...	81.698453	3.012857	1.002975
2	3500303	Aguaí	POLYGON ((-47.20890 -21.97129, -47.20297 -21.9...	52.933259	6.949182	1.228948
3	3500402	Águas da Prata	POLYGON ((-46.70755 -21.82895, -46.70380 -21.8...	42.664264	1.623514	0.156667
4	3500501	Águas de Lindóia	POLYGON ((-46.61147 -22.43496, -46.60302 -22.4...	77.279346	6.002794	4.217860
5	3500550	Águas de Santa Bárbara	POLYGON ((-49.19750 -22.72188, -49.19029 -22.7...	76.196319	7.510396	5.182174
6	3500600	Águas de São Pedro	POLYGON ((-47.87540 -22.59128, -47.86142 -22.6...	83.207825	2.363604	0.142857
7	3500709	Agudos	POLYGON ((-48.91106 -22.33760, -48.91545 -22.3...	77.315027	7.045012	4.528850
8	3500758	Alambari	POLYGON ((-47.80675 -23.48821, -47.79898 -23.4...	79.788360	6.859364	2.241165
9	3500808	Alfredo Marcondes	POLYGON ((-51.32568 -21.87177, -51.33163 -21.8...	75.207949	8.914508	4.767544
10	3500907	Altair	POLYGON ((-49.08594 -20.43743, -49.08830 -20.4...	83.404201	2.219665	3.265022
11	3501004	Altinópolis	POLYGON ((-47.41166 -20.80867, -47.40621 -20.8...	76.131045	6.009765	6.748079
12	3501103	Alto Alegre	POLYGON ((-50.14669 -21.52728, -50.14428 -21.5...	81.676869	4.079180	3.132840
13	3501152	Alumínio	POLYGON ((-47.31383 -23.46525, -47.30995 -23.4...	81.713879	3.941049	0.059359
14	3501202	Álvares Florence	POLYGON ((-49.95703 -20.17169, -49.95412 -20.1...	81.088438	3.589337	4.210002

#Plotando a figura
fig = px.box(fluxo_escolar, y="prop_aprovados_em", points = "all", hover_data=['name'], labels={'y':'gdp'})
fig.update_layout(title_text="Proporção de concluintes do ensino médio no estado de SP")
fig.show()

Analisando os dados através do novo gráfico podemos ver que o nível de aprovação do ensino médio no estado de São Paulo não é tão baixo e tem uma média de aprovação no ensino médio entre os municípios superior a 77 %.

# Plotando a figura
fig, ax = plt.subplots(figsize=(10, 10))
fluxo_escolar.plot( ax=ax, column='prop_aprovados_em', cmap='Blues', legend=True, legend_kwds={'shrink': 0.4})
ax.axis('off');
plt.title('Proporção de aprovados no ensino médio por munícipio no estado de SP', size=15); 

Podemos observar graças a esse gráfico que a maioria dos estados tem uma aprovação de alunos superior a 50 %, entretanto o que acontece com os alunos que não são aprovados ?

# Plotando a figura
fig, axs = plt.subplots(2, figsize=(14, 14))
fluxo_escolar.plot(ax=axs[0], column='prop_reprovados_em', cmap='Oranges', legend=True, legend_kwds={'label': "Porcentagem de reprovados", 'shrink': 0.6})
axs[0].set_title('Mapa do estado de SP com a proporção de reprovados no ensino médio', fontsize=15)

fluxo_escolar.plot(ax=axs[1], column='prop_abandono_em', cmap='Oranges', legend=True, legend_kwds={'label': "Porcentagem de abandonos", 'shrink': 0.6})
axs[1].set_title('Mapa do estado de SP com a proporção de abandonos no ensino médio', fontsize=15)

;

''

fluxo_escolar['prop_aprovados_em'].describe()
# 69.735006 é o primeiro quartil da prop_aprovados
flux_menor = fluxo_escolar[fluxo_escolar['prop_aprovados_em'] < 69.735006]
# Imprimindo a tabela comparativa
tab = flux_menor[['name', "prop_reprovados_em", "prop_abandono_em"]]
tab

	name	prop_reprovados_em	prop_abandono_em
2	Aguaí	6.949182	1.228948
3	Águas da Prata	1.623514	0.156667
15	Álvares Machado	12.203966	5.567830
18	Americana	4.560133	2.109248
21	Amparo	3.523008	3.350330
...	...	...	...
625	Urânia	1.736886	0.090037
630	Valparaíso	1.746424	4.329494
631	Vargem	10.590512	5.442857
632	Vargem Grande do Sul	6.850661	4.650581
635	Vera Cruz	1.948412	0.096667

171 rows × 3 columns

Pela tabela e os dois gráficos acima podemos supor que entre os alunos do ensino médio houve mais alunos que foram reprovados do que alunos que abadoram a escola. Entretanto nem o gráfico e nem a tabela nos permitem de confirrmarr esta hipótese.

# "Categorizando" a comparação entre a probabilidade de ser reprovado e abandonar.
mais_reprovados = (tab['prop_reprovados_em'] > tab['prop_abandono_em'])
print(f"Número de escolas com mais reprovações do que abandonos: {mais_reprovados.sum()}")
print(f"Número de escolas com mais abandonos do que reprovações: {len(mais_reprovados) - mais_reprovados.sum()}")

Número de escolas com mais reprovações do que abandonos: 159
Número de escolas com mais abandonos do que reprovações: 12

Graças a tabela comparativa acima é possível afirmar que na maioria dos municipios com baixa taxa de aprovação, os alunos não completaram o ensino médio devido principalmente a reprovação. Entretanto o que causa essa reprovação?

IDESP e INSE

Agora parte-se para as análises correlacionadas. Será que o Índice de nível socioeconômico das escolas (média) por munícipio está relacionado com as notas do IDESP?

Para respondermos essa pergunta, novamente criou-se uma nova tabela com os dados necessários.

# Unindo tabelas do INSE e do IDESP por id
IDESP_INSE=sp_nse.join(sp_idesp_2018_na, on="id", how="left")
IDESP_INSE

	nivel_socio_economico	ano	nota_idesp_ef_iniciais	nota_idesp_ef_finais	nota_idesp_em
id
3500105	4.000000	2018.0	NaN	3.423333	2.250000
3500204	4.190000	2018.0	NaN	NaN	3.170000
3500303	5.119091	2018.0	6.70	2.943333	2.436667
3500402	5.600000	2018.0	NaN	4.250000	3.110000
3500501	3.940000	2018.0	6.97	3.650000	3.630000
...	...	...	...	...	...
3557006	4.268462	2018.0	NaN	3.728462	2.754615
3557105	4.268889	2018.0	NaN	3.665556	2.413750
3557154	4.450000	2018.0	NaN	2.800000	2.650000
3557204	5.020000	2018.0	NaN	2.620000	2.560000
3557303	3.860000	2018.0	NaN	NaN	3.560000

644 rows × 5 columns

Ao plotar um gráfico de dispersão, esperava que os dados seguissem uma tendência de quanto menor fossem os INSE (já que quanto mais próximo de 0 melhor), maior seriam as notas do IDESP. Contudo, não é o que acontece.

#plotando gráfico de dispersão relacionando IDESP e INSE
fig=px.scatter(IDESP_INSE, x = "nivel_socio_economico", y = "nota_idesp_em", labels={"nivel_socio_economico": "INSE", "nota_idesp_em":"IDESP" })
fig.update_traces(marker=dict(size = 8,line = dict(width = 2)),selector=dict(mode = 'markers'))
fig.update_layout(title = 'IDESP X INSE')
fig.update_xaxes(title = 'índice de nível socioeconômico')
fig.update_yaxes(title = 'Notas IDESP - Ensino Médio')
fig.show()

# Calculando a correlação dos dados
Correlação= IDESP_INSE['nivel_socio_economico'].corr(IDESP_INSE['nota_idesp_em'])
print(f"Correlação dos dados:", Correlação)

Correlação dos dados: -0.11233666198194993

Ao analisar a correlação, ela é negativa (que até concordaria com a ideia de quanto melhor o INSE maior o IDESP), mas muita baixa (cerca de -0,11), portanto, não pode-se concluir que os dados possuem relação.

Segurança no Estado de São Paulo e INSE

Já Índice de nível socioeconômico das escolas (média) por munícipio está relacionado com a Segurança Pública de São Paulo?

INSE_SSP=pd.merge(sp_nse, df_seg_pop, how = 'inner', on = 'id')

# Plotando gráfico de dispersão relacionando IDESP e INSE
fig=px.scatter(INSE_SSP, x = "nivel_socio_economico", y = "proporcao", labels={"nivel_socio_economico": "INSE", "proporcao":"Proporção entre ocorrências e população" })
fig.update_traces(marker=dict(size = 8,line = dict(width = 2)),selector=dict(mode = 'markers'))
fig.update_layout(title = 'Segurança no Estado de São Paulo X INSE')
fig.update_xaxes(title = 'Índice de nível socioeconômico')
fig.update_yaxes(title = 'Segurança no Estado de São Paulo')
fig.show()

# Calculando a correlação dos dados
Correlação1= INSE_SSP['nivel_socio_economico'].corr(INSE_SSP['proporcao'])
print(f"Correlação dos dados:", Correlação1)

Correlação dos dados: -0.16829946816916258

Novamente o que temos é um resultado inesperado, afinal, em geral, pensa-se que quanto maior a quantidade de ocorrências, pior seria o INSE, contudo, mesmo havendo uma correlação negativa, ela novamente é muito baixa para afirmar uma relação entre os dados.

Segurança no Estado de São Paulo e Fluxo escolar

Uma das possíveis hipóteses que podemos formular é que o nível de reprovamento e abandono são correlacionados com o total ou com a proporção de crimes nos municípios do estado.

# Coletando os dados
df_seguranca=df_seguranca.groupby("id").mean()
# Excluindo dados desnecessários
flux2 = fluxo_escolar[fluxo_escolar.columns.difference(['geometry', 'description', 'name'])]
df_seguranca = df_seguranca[df_seguranca.columns.difference(['ano', 'mes'])]

# Transformando tipos de dados
sp_populacao.drop("id", axis="columns", inplace=True)
sp_populacao.rename(columns={"id_municipio": "id"}, inplace = 1)
sp_populacao["populacao"] = pd.to_numeric(sp_populacao["populacao"])
# Juntando as duas bases
df_seg_pop = pd.merge(sp_populacao, df_seguranca, how = 'inner', on = 'id')
df_seg_pop = df_seg_pop[df_seg_pop.columns.difference(['ano', 'sigla_uf', 'geometry'])]
df_seg_pop=df_seg_pop.groupby("id").mean()

# Selecionando dados específicos
df_seg_pop  = df_seg_pop[['populacao','estupro', 'furto_outros', 'homicidio_doloso', 'latrocinio','tentativa_de_homicidio','lesao_corporal_seguida_de_morte','lesao_corporal_dolosa','total_de_roubo_outros', 'roubo_de_veiculo']]
df2 = flux2.join(df_seg_pop, on="id", how="left")
# Normalizando os dados
abs_scaler = MaxAbsScaler()
abs_scaler.fit(df2)
abs_scaler.max_abs_
scaled_data = abs_scaler.transform(df2)
df_scaled = pd.DataFrame(scaled_data, columns=df2.columns)
df_scaled = df_scaled[df_scaled.columns.difference(['id'])]
df_scaled

	estupro	furto_outros	homicidio_doloso	latrocinio	lesao_corporal_dolosa	lesao_corporal_seguida_de_morte	populacao	prop_abandono_em	prop_aprovados_em	prop_reprovados_em	roubo_de_veiculo	tentativa_de_homicidio	total_de_roubo_outros
0	0.003117	0.001480	0.000539	0.001029	0.006603	0.000000	0.003092	0.141193	0.878739	0.526942	0.000013	0.001915	0.000056
1	0.000283	0.000222	0.000095	0.000000	0.000521	0.000000	0.000325	0.100834	0.924481	0.161527	0.000004	0.000361	0.000009
2	0.002550	0.002290	0.001774	0.004117	0.005089	0.000000	0.002814	0.123552	0.598981	0.372563	0.000424	0.004336	0.000379
3	0.001133	0.000241	0.000285	0.000000	0.000863	0.000000	0.000686	0.015750	0.482779	0.087041	0.000025	0.000108	0.000018
4	0.002834	0.001037	0.000760	0.001029	0.002282	0.000000	0.001502	0.424043	0.874476	0.321825	0.000165	0.001337	0.000194
...	...	...	...	...	...	...	...	...	...	...	...	...	...
640	0.012468	0.005090	0.007510	0.001544	0.011059	0.034091	0.009468	0.395383	0.901475	0.251456	0.000827	0.012828	0.001541
641	0.007651	0.007221	0.003137	0.001544	0.016711	0.000000	0.007395	0.358008	0.800251	0.386038	0.000113	0.007010	0.000557
642	0.000000	0.000171	0.000127	0.000000	0.000356	0.000000	0.000201	0.168818	0.944469	0.200831	0.000006	0.000181	0.000008
643	0.002550	0.000797	0.000634	0.001544	0.002396	0.011364	0.001135	0.759115	0.870202	0.237849	0.000015	0.001301	0.000042
644	0.000850	0.000716	0.000317	0.000515	0.002067	0.000000	0.000878	0.351134	0.904552	0.122470	0.000064	0.001048	0.000084

645 rows × 13 columns

# Plotando gráfico
corr = df_scaled.corr('spearman') 
f,ax = plt.subplots(figsize=(10, 10))
sns.heatmap(corr, cbar = True,  square = True, annot = True,
            xticklabels= True, yticklabels= True, linewidths=.5, ax=ax)

plt.title('Correlação entre total de ocorrências \n criminosas e fluxo escolar em SP', size=19);

# Calculando proporção de crimes
df_seg_pop2 = df_seg_pop.diff(axis  = 1)
# Juntando as duas bases
df3 = flux2.join(df_seg_pop2, on="id", how="left")
df3 = df3[df3.columns.difference(['populacao'])]
# Plotando gráfico
corr = df3.corr('spearman') 
f,ax = plt.subplots(figsize=(10, 10))
sns.heatmap(corr, cbar = True,  square = True, annot = True,
            xticklabels= True, yticklabels= True, linewidths=.5, ax=ax)

plt.title('Correlação entre proporção de ocorrências\n criminosas e fluxo escolar em SP ', size=19);

Ao plotar os dois gráficos é possível descobrir uma certa dependencia. O primeiro gráfico indica que a proporção de aprovação no ensino médio e as ocorências criminosas nos Municípios de SP tem uma correlação negativa abaixo de -0.31 para cada crime, isso evidência que há uma tendência de municípios com um total de crime menor a terem um número de aprovação maior.

Conclusões